﻿#region - Class details -
/*  
 *  Developer   : Wicus Botha
 *  Description : 
 *  History     : WB - 2008-10-20 - Created
 */
#endregion

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;
using IQ.Base.Services;
using IQ.DbBase;

namespace SqlServerAdmin 
{
	public class SqlMaintenance
	{
		#region # Constructor # 
		public SqlMaintenance()
		{
		}
		#endregion

		//#region - Private variables - 
		//#endregion 

		#region - Private methods - 
        private static void executeSqlCmd(string processDesc, string sql)
        {
            DbConnection globalConnection = DbHelper.GetConnection();
            if (globalConnection.State != ConnectionState.Open)
                globalConnection.Open();
            var cmd = globalConnection.CreateCommand();
            cmd.CommandTimeout = 0;
            try
            {
                // use Master
                cmd.CommandText = sql;
                object res = cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Logging.Error(processDesc + " error: " + e.Message);
                throw e;
                //return e.Message;
            }
        }
		#endregion 

		//#region - Protected variables - 
		//#endregion

		//#region - Protected methods - 
		//#endregion

		//#region - Public properties / variables - 
		//#endregion

		#region - Public methods - 
        public static void BackupDb(string DestDir, string DBName)
        {
            executeSqlCmd("Backup",
                          "BACKUP DATABASE " + DBName + " TO Disk='" + DestDir +"' with INIT"); // WITH STATS
        }

        public static void RestoreDb(string SrcFile, string DBName)
        {
            //  tmpSQL :string;
            string datName = ""; string datDest = "";
            string logName = ""; string logDest = "";                    
            //use spesific DB
            //get logic name & Location from sysfiles - name & filename
//            DataTable dbFiles = DbHelper.Load_Table("USE " + DBName + " SELECT fileid, name, filename FROM sysfiles ORDER BY 1");
            DataTable dbFiles = DbHelper.Load_Table("SELECT fileid, name, filename FROM " + DBName + "..sysfiles ORDER BY 1");
            if (dbFiles.Rows.Count > 1)
            {
                // data details
                if (Convert.ToInt32(dbFiles.Rows[0]["FileId"]) == 1)
                    datDest = dbFiles.Rows[0]["filename"].ToString();
                 // log details
                if (Convert.ToInt32(dbFiles.Rows[1]["FileId"]) == 2)
                    logDest = dbFiles.Rows[1]["filename"].ToString();
            }
            //restore filelistonly from Disk='D:\DataSQLBackup'
            dbFiles = DbHelper.Load_Table("Restore FileListOnly FROM DISK = '" + SrcFile + "'");
            datName = dbFiles.Rows[0]["LogicalName"].ToString().Trim();
            logName = dbFiles.Rows[1]["LogicalName"].ToString().Trim();
            // RunQry(qryToUse,'use Master','E'); // for exclusive use
            DbConnection globalConnection = DbHelper.GetConnection();
            var cmd = globalConnection.CreateCommand();
            if (globalConnection.State != ConnectionState.Open)
                globalConnection.Open();
            cmd.CommandTimeout = 0;
            try
            {
                // use Master
                executeSqlCmd("Restore","RESTORE DATABASE " + DBName +
                    " FROM DISK = '" + SrcFile + "' " +
                    " WITH MOVE '" + datName + "' TO '" + datDest + "'," +
                    " MOVE '" + logName + "' TO '" + logDest + "',REPLACE"); // WITH STATS                                          
            }
            catch (Exception e)
            {
                Logging.Error("Restore error: " + e.Message);
                IQ.BaseWindows.General.MesDlg("Could not restore database, error: " + e.Message, 'E');
                //return e.Message;
            }
            globalConnection.Close();
            //  MesDlg('Restore database '+DBName+' completed.'+'  Execute time: '+FCMDtime,'I');
        }

        public static void DropDb(string DBName)
        {
            executeSqlCmd("Drop database", "DROP DATABASE " + DBName);
        }

        public static void CreateDb(string path, string DBName)
        {
            string tmpDir = path;
            // Add last '\' in
            if (tmpDir.Trim().Length > 0)
            {                
                if (tmpDir[tmpDir.Length-1] != '\\')
                    tmpDir += '\\';
            }
             executeSqlCmd("Create Db","CREATE DATABASE " + DBName +" ON" +
                    "(NAME = " + DBName + "_dat,"+
                    " FILENAME = '" + tmpDir + DBName + "_dat.mdf'," +
//                    " SIZE = 10MB, MAXSIZE = 500MB, FILEGROWTH = 5MB) " +
                    " SIZE = 10MB, FILEGROWTH = 5MB) " + // un-restricted growth
                    " LOG ON " +
                    "(NAME = '" + DBName + "_log'," +
                    " FILENAME =  '" + tmpDir + DBName + "_log.ldf'," +
                    " SIZE = 5MB, MAXSIZE = 1000MB, FILEGROWTH = 5MB)");
             executeSqlCmd("Set recovery model", "ALTER DATABASE [" + DBName + "] SET RECOVERY SIMPLE");
        }

        public static void ShrinkDb(string DBName, string freeSpace)
        {
            executeSqlCmd("Shrink database", "DBCC shrinkdatabase (" + DBName + "," + freeSpace + ")");            
        }

        public static void Kill(string spId)
        {
            executeSqlCmd("Kill", "KILL " + spId);
        }
	    public static void Kill_AppConnections(string appName)
        {
            DataTable dt = DbHelper.Load_Table(SqlQueries.GetAciveConnectionsForApp(appName));
            foreach (DataRow dr in dt.Rows)
            {
                try
                {
                    executeSqlCmd("Kill", "KILL " + dr[0].ToString());
                }
                catch //(Exception e)
                {
                    //Logging.Error("M=" + e.Message);
                }
            }
        }
        #endregion
	}
}